﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kona.Model;
using Kona.Infrastructure;
using System.Data;
using System.Data.Common;

namespace Kona.SimpleSqlRepository {
    public class SimpleCMSRepository:ICMSRepository {
        string connectionStringName = "KonaConnectionString";
        #region ICMSRepository Members

        public Page GetPage(string slug, string languageCode) {
            
            //get the ID for the requested page
            var sql = PagesTable.Select("PageID").Where("url", slug).And("LanguageCode", languageCode);
            object id = sql.BuildCommand().ExecuteScalar();
            Page result = null;
            
            if (id != null) {
                result = GetPage((Guid)id);
            }


            return result;
        }

        public Page GetPage(Guid id) {
            Page result = new Page();
            //get the page

            var batch = new BatchSql();

            batch.Append(PagesTable.Select().Where(PagesTable.ColumnsQualified.PageID, id));

            
            //products for widgets
            //if this scares you, you might want to avert your eyes :p
            var widgetProductSql = GetProductWidgetSql();
            widgetProductSql.Add("\r\n WHERE "+WidgetsTable.ColumnsQualified.WidgetID);
            widgetProductSql.Add("\r\n IN (SELECT " + WidgetsTable.ColumnsQualified.WidgetID + " WHERE " + WidgetsTable.ColumnsQualified.PageID + "=@p0)\r\n");
            

            //add it to the page sql
            batch.Append(widgetProductSql);
            

            batch.Append(WidgetsTable.Select()
                .Where(WidgetsTable.ColumnsQualified.PageID, id)
                .OrderBy(WidgetsTable.ColumnsQualified.ListOrder));


            var cmd = batch.BuildCommand(connectionStringName);
            //cmd.AddParameter("@pageid", id, DbType.Guid);
            using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {

                //first result: page
                if (rdr.Read()) {
                    result=LoadPage(rdr);
                }

                //second result - widgets
                if (rdr.NextResult()) {
                    result.Widgets = LoadWidgets(rdr);
                }

            }
            
            
            return result;

        }
        Page LoadPage(DbDataReader rdr) {
            Page result = new Page();
            result.CreatedBy = PagesTable.ReadCreatedBy(rdr);
            result.CreatedOn = PagesTable.ReadCreatedOn(rdr);
            result.LanguageCode = PagesTable.ReadLanguageCode(rdr);
            result.ModifiedBy = PagesTable.ReadModifiedBy(rdr);
            result.ModifiedOn = PagesTable.ReadModifiedOn(rdr);
            result.PageID = PagesTable.ReadPageID(rdr);
            result.Slug = PagesTable.ReadSlug(rdr);
            result.Title = PagesTable.ReadTitle(rdr);
            result.Url = PagesTable.ReadUrl(rdr);
            result.Status = (PublishStatus)PagesTable.ReadPageStatusID(rdr);
            return result;
        }


        SqlStatement GetProductWidgetSql() {
            var sql = new SqlStatement(connectionStringName);
            //products for widgets
            sql.Add(string.Format("SELECT {0},{1}", Widgets_ProductsTable.ColumnsQualified.WidgetID, ProductsTable.COLUMN_LIST));
            sql.Add("\r\nFROM " + WidgetsTable.TABLE_NAME + " \r\nINNER JOIN ");
            sql.Add(string.Format("{0} ON {1} = {2} \r\nINNER JOIN {3} ON {4} = {5}",
                Widgets_ProductsTable.TABLE_NAME,
                WidgetsTable.ColumnsQualified.WidgetID,
                Widgets_ProductsTable.ColumnsQualified.WidgetID,
                ProductsTable.TABLE_NAME,
                Widgets_ProductsTable.ColumnsQualified.SKU,
                ProductsTable.ColumnsQualified.SKU))
                .OrderBy(Widgets_ProductsTable.ColumnsQualified.listOrder);

            return sql;
        }

        /// <summary>
        /// This method assumes you're sending in a Reader with two result sets - the first
        /// should be Products for the Widgets, the second the Widget data
        /// </summary>
        /// <param name="rdr"></param>
        /// <returns></returns>
        IList<IWidget> LoadWidgets(DbDataReader rdr) {
            IList<IWidget> result = new List<IWidget>();
            var productRepo = new SimpleProductRepository();

            //second result: products for widgets
            var widgetsWithProducts = new Dictionary<Guid, List<Product>>();
            var products = new List<Product>();
            while (rdr.Read()) {
                var product = productRepo.LoadProduct(rdr);
                var widgetID = Widgets_ProductsTable.ReadWidgetID(rdr);
                
                if (!widgetsWithProducts.ContainsKey(widgetID))
                    widgetsWithProducts.Add(widgetID, new List<Product>());

                widgetsWithProducts[widgetID].Add(product);
            }

            //third result: widgets
            if (rdr.NextResult()) {
                while (rdr.Read()) {
                    var widgetID = WidgetsTable.ReadWidgetID(rdr);
                    IWidget widget = new Widget() ;
                    widget.IsTyped = WidgetsTable.ReadIsTyped(rdr);

                    if (widget.IsTyped) {
                        //we only have a single type here
                        var typedWidget = new Widget<IList<Product>>();
                        if (widgetsWithProducts.ContainsKey(widgetID))
                            typedWidget.Data = widgetsWithProducts[widgetID];
                        else
                            typedWidget.Data = new List<Product>();
                        widget = typedWidget;
                    } else {
                        widget = new Widget();
                    }

                    //read the reader data
                    widget.ID = WidgetsTable.ReadWidgetID(rdr);
                    widget.Body = WidgetsTable.ReadBody(rdr);
                    widget.Title = WidgetsTable.ReadTitle(rdr);
                    widget.PageID = WidgetsTable.ReadPageID(rdr);
                    widget.ListOrder = WidgetsTable.ReadListOrder(rdr);
                    widget.ViewName = WidgetsTable.ReadViewName(rdr);
                    widget.EditorName = WidgetsTable.ReadEditorName(rdr);
                    widget.Zone = WidgetsTable.ReadZone(rdr);
                    widget.LanguageCode = WidgetsTable.ReadLanguageCode(rdr);
                    widget.IsTyped = WidgetsTable.ReadIsTyped(rdr);
                    result.Add(widget);
                }
            }
            return result;
        }
        /// <summary>
        /// Returns a single widget
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<IWidget> GetWidgets(string zone, Guid pageID) {
            var batch = new BatchSql();
            batch.Append(GetProductWidgetSql().Where(WidgetsTable.ColumnsQualified.PageID, pageID));

            batch.Append(WidgetsTable.Select()
                .Where(WidgetsTable.ColumnsQualified.PageID, pageID)
                .OrderBy(WidgetsTable.ColumnsQualified.ListOrder));

            IList<IWidget> result = new List<IWidget>();
            var cmd = batch.BuildCommand(connectionStringName);
            using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {

                result = LoadWidgets(rdr);
            }

            return result;
        }
        /// <summary>
        /// Returns a single widget
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IWidget GetWidget(Guid id) {
            var batch = new BatchSql();
            batch.Append( GetProductWidgetSql().Where(WidgetsTable.ColumnsQualified.WidgetID, id));
            batch.Append(WidgetsTable.Select().Where(WidgetsTable.ColumnsQualified.WidgetID, id));

            IWidget result = null;
            var cmd = batch.BuildCommand(connectionStringName);
            using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {

                var list = LoadWidgets(rdr);
                if (list.Count > 0)
                    result = list[0];
            }

            return result;
        }

        public void Create(Page pg) {
            var sql = PagesTable.Insert(new Dictionary<string, object>()
            {
                {PagesTable.Columns.Url,pg.Url},
                {PagesTable.Columns.LanguageCode,pg.LanguageCode},
                {PagesTable.Columns.Title,pg.Title},
                {PagesTable.Columns.Slug,pg.Slug},
                {PagesTable.Columns.ModifiedBy,pg.ModifiedBy},
                {PagesTable.Columns.ModifiedOn,pg.ModifiedOn},
                {PagesTable.Columns.PageStatusID,(int)pg.Status},
                {PagesTable.Columns.CreatedBy,pg.CreatedBy},
                {PagesTable.Columns.CreatedOn,pg.CreatedOn},
                {PagesTable.Columns.PageID,pg.PageID}
           });

            using (var command = sql.BuildCommand()) {
                command.ExecuteNonQuery();
            }
        }

        public void Update(Page pg) {

            
            //see if this page exists
            var sql=PagesTable.Update(new Dictionary<string,object>(){
                        {PagesTable.Columns.Title,pg.Title},
                        {PagesTable.Columns.PageStatusID,(int)pg.Status},
                        {PagesTable.Columns.Slug,pg.Slug},
                        {PagesTable.Columns.Url,pg.Slug}

                }).Where(PagesTable.Columns.PageID, pg.PageID);

            sql.BuildCommand().ExecuteNonQuery();
        }

        public void SaveWidget(IWidget widget) {
            //see if exists
            bool exists = SqlHelper.RecordExists(connectionStringName, WidgetsTable.TABLE_NAME, WidgetsTable.ColumnsQualified.WidgetID, widget.ID);
            var sql = new SqlStatement(connectionStringName);
            
            if (exists) {
                sql = WidgetsTable.Update(new Dictionary<string,object>(){
                    {WidgetsTable.Columns.ListOrder,widget.ListOrder},
                    {WidgetsTable.Columns.Zone,widget.Zone},
                    {WidgetsTable.Columns.Title,widget.Title},
                    {WidgetsTable.Columns.Body,widget.Body}
                }).Where(WidgetsTable.ColumnsQualified.WidgetID, widget.ID);

            } else {

                //pull the list order and increment it
                sql.Add(string.Format("SELECT MAX(listOrder) FROM {0}", WidgetsTable.TABLE_NAME))
                    .Where(WidgetsTable.ColumnsQualified.PageID, widget.PageID)
                    .And(WidgetsTable.ColumnsQualified.Zone, widget.Zone);

                int listOrder = 0;
                object existing = sql.BuildCommand().ExecuteScalar();
                if (existing != null) {
                    int.TryParse(existing.ToString(), out listOrder);
                }

                sql = WidgetsTable.Insert(new Dictionary<string, object>(){
                    {WidgetsTable.Columns.PageID,widget.PageID},
                    {WidgetsTable.Columns.ViewName,widget.ViewName},
                    {WidgetsTable.Columns.EditorName,widget.EditorName},
                    {WidgetsTable.Columns.ListOrder,(int)listOrder+1},
                    {WidgetsTable.Columns.Zone,widget.Zone},
                    {WidgetsTable.Columns.Title,widget.Title},
                    {WidgetsTable.Columns.Body,widget.Body},
                    {WidgetsTable.Columns.LanguageCode,widget.LanguageCode},
                    {WidgetsTable.Columns.WidgetID,widget.ID},
                    {WidgetsTable.Columns.IsTyped,widget.IsTyped}
                });       
            }
            var cmd = sql.BuildCommand();
            //save em
            cmd.ExecuteNonQuery();
        }

        public void DeletePage(string slug) {
            var sql = PagesTable.Select(PagesTable.Columns.PageID)
                .Where(PagesTable.ColumnsQualified.Slug, slug);
            object result = sql.BuildCommand().ExecuteScalar();
            if (result != null) {
                DeletePage((Guid)result);
            }

        }
        public void DeleteWidget(Guid id) {

            var batch = new BatchSql();

            batch.Append(Widgets_ProductsTable.Delete()
                .Where(WidgetsTable.Columns.WidgetID, id));

            batch.Append(WidgetsTable.Delete()
                .Where(WidgetsTable.Columns.WidgetID, id));

            batch.BuildCommand(connectionStringName).ExecuteNonQuery();
        }

        public void DeletePage(Guid id) {
            var commands = new SortedList<int, DbCommand>();
            
            //remove any product associations
            var productSql = (Widgets_ProductsTable.Delete());
            productSql.Add("WHERE widgetid IN (SELECT widgetid FROM Widgets WHERE PageID=@pageid)");
            
            var cmd = productSql.BuildCommand();
            cmd.AddParameter("@pageid", id, DbType.Guid);
            
            var widgetsql = WidgetsTable.Delete()
                .Where(WidgetsTable.ColumnsQualified.PageID, id);

            var pageSql = PagesTable.Delete()
                .Where(PagesTable.ColumnsQualified.PageID, id);

            //use a sorted list to be sure these are executed
            //in order
            commands.Add(1,cmd);
            commands.Add(2,widgetsql.BuildCommand());
            commands.Add(3,pageSql.BuildCommand());

            //transactional
            SqlHelper.Execute(commands,connectionStringName);
            

        }

        public IList<Page> GetUnpublishedPages() {
            var result = new List<Page>();

            var sql = PagesTable.Select()
                .Where(PagesTable.ColumnsQualified.PageStatusID,Op.NotEquals, 2)
                .OrderBy(PagesTable.ColumnsQualified.ListOrder);

            using (var rdr = sql.BuildCommand().ExecuteReader(CommandBehavior.CloseConnection)) {
                while (rdr.Read()) {
                    result.Add(LoadPage(rdr));
                }
            }

            return result;
        }

        public IList<Page> GetPages() {
            var result = new List<Page>();

            var sql = PagesTable.Select()
                .OrderBy(PagesTable.ColumnsQualified.ListOrder);

            using (var rdr = sql.BuildCommand().ExecuteReader(CommandBehavior.CloseConnection)) {
                while (rdr.Read()) {
                    result.Add(LoadPage(rdr));
                }
            }

            return result;
        }

        public IList<Page> GetPages(PublishStatus status) {
            var result = new List<Page>();

            var sql = PagesTable.Select()
                .Where(PagesTable.ColumnsQualified.PageStatusID, (int)status)
                .OrderBy(PagesTable.ColumnsQualified.ListOrder);

            using (var rdr = sql.BuildCommand().ExecuteReader(CommandBehavior.CloseConnection)) {
                while (rdr.Read()) {
                    result.Add(LoadPage(rdr));
                }
            }

            return result;
        }
        public void SaveProductWidget(Guid widgetID, string[] skus) {

            var commands = new List<DbCommand>();
            //delete out all associations
            var sql = Widgets_ProductsTable.Delete()
                .Where(Widgets_ProductsTable.ColumnsQualified.WidgetID, widgetID);
            
            commands.Add(sql.BuildCommand());
            //add a command for each pass-in sku
            int listOrder = 0;
            foreach (string sku in skus) {
                var wpSql=Widgets_ProductsTable.Insert(new Dictionary<string, object>()
                {
                    {Widgets_ProductsTable.ColumnsQualified.WidgetID, widgetID},
                    {Widgets_ProductsTable.ColumnsQualified.SKU, sku},
                    {Widgets_ProductsTable.ColumnsQualified.listOrder, listOrder},
                });

                var cmd = wpSql.BuildCommand();
                commands.Add(cmd);
                listOrder++;
            }

            if (commands.Count > 0)
                SqlHelper.Execute(commands, connectionStringName);

        }

        public void OrderWidgets(string[] widgetIDs) {
            var commands = new List<DbCommand>();
            int listOrder = 0;
            foreach (string id in widgetIDs) {

                var sql = WidgetsTable.Update(new Dictionary<string, object>()
                {
                    {WidgetsTable.Columns.ListOrder,listOrder}
                }).Where(WidgetsTable.Columns.WidgetID,new Guid(id));
                
                commands.Add(sql.BuildCommand());
                sql.Clear();
                listOrder++;
            }
            if(commands.Count>0)
                SqlHelper.Execute(commands, connectionStringName);
        }

        #endregion


        public void OrderPages(string[] pageIDs) {
            var commands = new List<DbCommand>();
            int listOrder = 0;
            foreach (string id in pageIDs) {

                var sql = PagesTable.Update(new Dictionary<string, object>()
                {
                    {PagesTable.Columns.ListOrder,listOrder}
                }).Where(PagesTable.Columns.PageID, new Guid(id));

                commands.Add(sql.BuildCommand());
                sql.Clear();
                listOrder++;
            }
            if (commands.Count > 0)
                SqlHelper.Execute(commands, connectionStringName);
        }

    }
}
